package com.tongzhou.tongzhou.sales.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.tongzhou.sales.dao.ISalesDAO;
import com.tongzhou.tongzhou.sales.model.Sales;
import com.tongzhou.tongzhou.sales.model.SearchSalesCondition;
import com.tongzhou.util.Util;

public class SalesDAO implements ISalesDAO{

	@Override
	public List<Sales> getSalesList(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,image_url,title,content,quotCall,status,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time ");
		sql.append(" from sales ");
		sql.append(" where status='Y' ");
		if(id!=0){
			sql.append(" and id='"+id+"'");
		}
		List<Sales> result=DBUtil.query(sql.toString(),new BeanListHandler<Sales>(Sales.class));
		return result;
	}
	@Override
	public List<Sales> getSalesListByPage(SearchSalesCondition condition)
			throws DBException {
		List<Sales> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(condition.getNewNo() !=0){
			sqlCondition.append(" and id >?");
			paramList.add(String.valueOf(condition.getNewNo()));
		}
		if(condition.getType()!=null && !condition.getType().equals("")){
			sqlCondition.append(" and type=?");
			paramList.add(String.valueOf(condition.getType()));
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(id) ");
		sql.append(" from sales ");
		sql.append(" where status='Y' ");
		sql.append(" and start_time<=now() and end_time>=now()");
		sql.append(sqlCondition);
		
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select id,image_url,title,content,quotCall,status,header,type,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time ");
		sql.append(" from sales ");
		sql.append(" where status='Y' ");
		sql.append(" and start_time<=now() and end_time>=now()");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Sales>(Sales.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}
	
	@Override
	public int addSales(Sales sales) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into sales(image_url,title,content,quotCall,status,create_time,start_time,end_time,header,type) ");
		sql.append("values('"+Util.changeToObj(sales.getImage_url())+"',");
		sql.append("'"+Util.changeToObj(sales.getTitle())+"',");
		sql.append("'"+Util.changeToObj(sales.getContent())+"',");
		sql.append("'"+Util.changeToObj(sales.getQuotCall())+"',");
		sql.append("'"+Util.changeToObj(sales.getStatus())+"',");
		sql.append("now(),");
		sql.append("'"+Util.changeToObj(sales.getStart_time())+"',");
		sql.append("'"+Util.changeToObj(sales.getEnd_time())+"',");
		sql.append("'"+Util.changeToObj(sales.getHeader())+"',");
		sql.append("'"+Util.changeToObj(sales.getType())+"');");
		int result=DBUtil.update(sql.toString());
		return result;
	}
	@Override
	public void deleteSales(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sales where id='"+id+"'");
		DBUtil.update(sql.toString());
	}
	@Override
	public Sales getSalesById(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  id,image_url,title,content,quotCall,status,header,type,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time,");
		sql.append("date_format(start_time,'%Y-%m-%d') as start_time,");
		sql.append("date_format(end_time,'%Y-%m-%d') as end_time ");
		sql.append(" from sales ");
		sql.append(" where id='"+id+"'");
		Sales sales=DBUtil.query(sql.toString(), new BeanHandler<Sales>(Sales.class));
		return sales;
	}
	@Override
	public List<Sales> listSales(SearchSalesCondition condition)
			throws DBException {
		List<Sales> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(condition.getTitle()!=null && !condition.getTitle().equals("")){
			sqlCondition.append(" and title like ?");
			paramList.add("'%"+condition.getTitle()+"%'");
		}
		if(condition.getType()!=null && !condition.getType().equals("")){
			sqlCondition.append(" and type=?");
			paramList.add(String.valueOf(condition.getType()));
		}
		if(condition.getStatus()!=null && !condition.getStatus().equals("")){
			sqlCondition.append(" and status=?");
			paramList.add(String.valueOf(condition.getStatus()));
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(id) ");
		sql.append(" from sales ");
		sql.append(" where status='Y' ");
		sql.append(sqlCondition);
		
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select id,image_url,title,content,quotCall,status,header,type,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time,");
		sql.append("date_format(start_time,'%Y-%m-%d') as start_time,");
		sql.append("date_format(end_time,'%Y-%m-%d') as end_time ");
		sql.append(" from sales ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Sales>(Sales.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}
	@Override
	public void updateSales(Sales sales) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sales set ");
		sql.append("image_url='"+Util.changeToObj(sales.getImage_url())+"',");
		sql.append("title='"+Util.changeToObj(sales.getTitle())+"',");
		sql.append("content='"+Util.changeToObj(sales.getContent())+"',");
		sql.append("quotCall='"+Util.changeToObj(sales.getQuotCall())+"',");
		sql.append("status='"+Util.changeToObj(sales.getStatus())+"',");
		sql.append("start_time='"+Util.changeToObj(sales.getStart_time())+"',");
		sql.append("end_time='"+Util.changeToObj(sales.getEnd_time())+"',");
		sql.append("header='"+Util.changeToObj(sales.getHeader())+"',");
		sql.append("type='"+Util.changeToObj(sales.getType())+"' ");
		sql.append("where id='"+sales.getId()+"'");
		DBUtil.update(sql.toString());
	}
}
